package com.ruoyi.util.mysqljdbc;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author syc
 * @Date Created in 14:42 2022/8/12
 * @Description 使用jdbc 链接mysql 8
 */

//  数据库设置自动提交
//  connection.setAutoCommit();
//  事务提交
//  connection.commit();
//  事务回滚
//  connection.rollback();

public class Mysql8jdbc {
	public static final String driverMysql8 = "com.mysql.cj.jdbc.Driver";
	public static final String driverMysql5 = "com.mysql.jdbc.Driver";

	public enum EditOrselect {
		select, count, edit;
	}

	public static JSONObject jdbcGetJSON(String DriverName, String local, String port, String database, String dbAccount,
			String dbpassword, String sql, EditOrselect editOrselect) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		JSONObject jSONObject = new JSONObject();
		// 1. 加载驱动
		try {
			Class.forName(DriverName);
			// 2. 创建连接对象
			connection = DriverManager.getConnection(
					"jdbc:mysql://" + local + ":" + port + "/" + database + "?useSSL=false&serverTimezone=UTC",
					dbAccount, dbpassword);
			// 3.执行sql使用PreparedStatement对象
			statement = connection.prepareStatement(sql);
			// 可以进行设置参数 防止sql注入 (自己用的,不存在注入的问题)
			// 4.获取结果
			if (editOrselect.name().equals(EditOrselect.select.name())) {
				// 查询操作返回ResultSet
				rs = statement.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				JSONArray jsonArray = new JSONArray();
				while (rs.next()) {
					JSONObject jsonObj = new JSONObject();
					// 遍历每一列
					for (int i = 1; i <= columnCount; i++) {
						String columnName = metaData.getColumnLabel(i);
						String value = rs.getString(columnName);
						jsonObj.put(columnName, value);
					}
					jsonArray.add(jsonObj);
				}
				jSONObject.put(EditOrselect.select.name(), jsonArray);
			} else if (editOrselect.name().equals(EditOrselect.edit.name())) {
				// 更新、插入、删除，都是用这个，返回一个受影响的行数
				int num = statement.executeUpdate();
				jSONObject.put(EditOrselect.edit.name(), num);
			} else if (editOrselect.name().equals(EditOrselect.count.name())) {
				rs = statement.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				while (rs.next()) {
					// 遍历每一列
					String columnName = metaData.getColumnLabel(1);
					String value = rs.getString(columnName);
					jSONObject.put(EditOrselect.count.name(), value);
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.关闭资源
			try {
				if (rs != null)
					rs.close();
				if (statement != null)
					statement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return jSONObject;
	}

	/**
	 * @Description:这个是为生成sql脚本写的jdbc,
	 * @author syc
	 * @date: 2022年9月15日 下午8:37:46
	 */
	public static List<String> jdbcCreactSql(String DriverName, String local, String port, String database, String dbAccount,
			String dbpassword, String sql, String tableName) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		List<String> listdata = new ArrayList<String>();
		// 1. 加载驱动
		try {
			Class.forName(DriverName);
			// 2. 创建连接对象
			connection = DriverManager.getConnection(
					"jdbc:mysql://" + local + ":" + port + "/" + database + "?useSSL=false&serverTimezone=UTC",
					dbAccount, dbpassword);
			// 3.执行sql使用PreparedStatement对象
			statement = connection.prepareStatement(sql);
			// 可以进行设置参数 防止sql注入 (自己用的,不存在注入的问题)
			// 4.获取结果
			// 查询操作返回ResultSet
			rs = statement.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				StringBuffer ColumnName = new StringBuffer();
				StringBuffer ColumnValue = new StringBuffer();
				StringBuffer insertSQL = new StringBuffer();
				for (int i = 1; i <= columnCount; i++) {
					String value = null;
					if (rs.getString(i) != null) {
						value = rs.getString(i).trim();
					}
					if (null != value && "".equals(value)) {
						value = "";
					}	
				    if(i > 1) {
				    	ColumnName.append(",");
				    	ColumnValue.append(",");
				    }
				    ColumnName.append(metaData.getColumnName(i));
				    if(value == null) {
				    	ColumnValue.append(value);
				    }else {
						if (Types.CHAR == metaData.getColumnType(i) || Types.VARCHAR == metaData.getColumnType(i)
								|| Types.LONGVARCHAR == metaData.getColumnType(i)) {
							ColumnValue.append("'").append(value).append("'");
						} else if (Types.SMALLINT == metaData.getColumnType(i)
								|| Types.INTEGER == metaData.getColumnType(i)
								|| Types.BIGINT == metaData.getColumnType(i)
								|| Types.FLOAT == metaData.getColumnType(i)
								|| Types.DOUBLE == metaData.getColumnType(i)
								|| Types.NUMERIC == metaData.getColumnType(i)
								|| Types.DECIMAL == metaData.getColumnType(i)
								|| Types.TINYINT == metaData.getColumnType(i)) {
							ColumnValue.append(value);
						} else if (Types.DATE == metaData.getColumnType(i)
									|| Types.TIME == metaData.getColumnType(i)
									|| Types.TIMESTAMP == metaData.getColumnType(i)) {
							ColumnValue.append("'").append(value).append("'");
						} else {
							ColumnValue.append(value);
						}
				    }
				}
				insertSQL.append("INSERT INTO").append(" ").append("`" + database + "`").append(".")
	                .append("`" + tableName + "`").append("(").append(ColumnName.toString()).append(")").append("VALUES").append("(").append(ColumnValue.toString()).append(");");
				listdata.add(insertSQL.toString());
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.关闭资源
			try {
				if (rs != null)
					rs.close();
				if (statement != null)
					statement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listdata;
	}

	// 测试
//	public static void main(String[] args) {
//		// String sql = "select a.user_account,a.user_xm,b.userkz_pwd,b.userkz_pwdcheck
//		// from sysm_user a, sysm_userkz b where a.userid = b.userkz_userid";
//		String sql = "select count(1) from sysm_user a, sysm_userkz b where a.userid = b.userkz_userid";
//		JSONObject json = Mysql8jdbc.jdbcGetJSON(Mysql8jdbc.driverMysql8, "192.168.1.163", "3316", "syzhright", "root",
//				"sykj85575700", sql, EditOrselect.count);
//
//		System.out.println(json.toString());
//		// System.out.println( "111222" + EditOrselect.edit.name());
//
//	}
}
